This tutorial explains how to complete the Excel export of student data using Magicodes.
- Export Features
- How to export Excel table headers
- How to export data、cut data、use filters
- Support dynamic/ExpandoObject type of dynamic export
- Name： Name (current Sheet name)
- HeaderFontSize：Header font size
- FontSize：Body font size
- MaxRowNumberOnASheet：The maximum number of rows allowed for a Sheet, after setting it, multiple Sheets will be output.
- TableStyle：Form Style
- AutoFitAllColumn：bool Adaptive all columns
- Author：Author
- ExporterHeaderFilter：Head Screener
- AutoCenter：Set to center the entire table
- DisplayName： Display Name
- FontSize： Font Size
- IsBold： Bolded or not
- Format： Formatting
- IsAutoFit： Adaptive or not
- IsIgnore： Whether or not to ignore
- AutoCenterColumn： Set column centering
Works on properties and enumeration members.
- IsImportIgnore：Whether to ignore the import, default true
- IsExportIgnore：Whether to ignore export, default true
- Description： Sheet Import Export Annotation
- DataFormatString：Data display format
- Name
- Import exported display column names (properties)
- Order
- 列索引
- Description
- 列描述
Install-Package Magicodes.IE.Excel- Exporting through arrays
public async Task ExportHeader()
{
IExcelExporter exporter = new ExcelExporter();
var filePath = "h.xlsx";
var arr = new[] { "Name1", "Name2", "Name3", "Name4", "Name5", "Name6" };
var sheetName = "Test";
var result = await exporter.ExportHeaderAsByteArray(arr, sheetName);
result.ToExcelExportFileInfo(filePath);
}- Export via DTO
public async Task ExportHeader()
{
IExporter exporter = new ExcelExporter();
var filePath = "h.xlsx";
var result = await exporter.ExportHeaderAsByteArray<Student>( new Student());
result.ToExcelExportFileInfo(filePath);
}
- Basic Export
public class Student
{
/// <summary>
/// 姓名
/// </summary>
public string Name { get; set; }
/// <summary>
/// 年龄
/// </summary>
public int Age { get; set; }
}
public async Task Export()
{
IExporter exporter = new ExcelExporter();
var result = await exporter.Export("a.xlsx", new List<Student>()
{
new Student
{
Name = "MR.A",
Age = 18
},
new Student
{
Name = "MR.B",
Age = 19
},
new Student
{
Name = "MR.B",
Age = 20
}
});
}
With the code as above we can export Excel as shown below
- Example of feature export
public async Task Export()
{
IExporter exporter = new ExcelExporter();
var result = await exporter.Export("test.xlsx", new List<Student>()
{
new Student
{
Name = "MR.A",
Age = 18,
Remarks = "我叫MR.A,今年18岁",
Birthday=DateTime.Now
},
new Student
{
Name = "MR.B",
Age = 19,
Remarks = "我叫MR.B,今年19岁",
Birthday=DateTime.Now
},
new Student
{
Name = "MR.C",
Age = 20,
Remarks = "我叫MR.C,今年20岁",
Birthday=DateTime.Now
}
});
}
/// <summary>
/// 学生信息
/// </summary>
[ExcelExporter(Name = "学生信息", TableStyle = "Light10", AutoFitAllColumn = true, MaxRowNumberOnASheet = 2)]
public class Student
{
/// <summary>
/// 姓名
/// </summary>
[ExporterHeader(DisplayName = "姓名")]
public string Name { get; set; }
/// <summary>
/// 年龄
/// </summary>
[ExporterHeader(DisplayName = "年龄")]
public int Age { get; set; }
/// <summary>
/// 备注
/// </summary>
public string Remarks { get; set; }
/// <summary>
/// 出生日期
/// </summary>
[ExporterHeader(DisplayName = "出生日期", Format = "yyyy-mm-DD")]
public DateTime Birthday { get; set; }
}
With the code as above we can export Excel as shown below
- ExcelExporter can set the export layout settings, table format, SheetName, adaptive columns, etc. Refer to 导出特性
- ExporterHeader can set the table header name, style, etc. For details, refer to 导出特性
- ExcelExporter MaxRowNumberOnASheet can split the data, specify the length of the Sheet data by this property to achieve automatic cutting
- Table Header Filter
/// <summary>
/// 学生信息
/// </summary>
[ExcelExporter(Name = "学生信息", TableStyle = "Light10", AutoFitAllColumn = true, MaxRowNumberOnASheet = 2,ExporterHeaderFilter = typeof(ExporterStudentHeaderFilter))]
public class Student
{
/// <summary>
/// 姓名
/// </summary>
[ExporterHeader(DisplayName = "姓名")]
public string Name { get; set; }
/// <summary>
/// 年龄
/// </summary>
[ExporterHeader(DisplayName = "年龄",Format = "#,##0")]
public int Age { get; set; }
/// <summary>
/// 备注
/// </summary>
public string Remarks { get; set; }
}
public class ExporterStudentHeaderFilter : IExporterHeaderFilter
{
/// <summary>
/// 表头筛选器（修改名称）
/// </summary>
/// <param name="exporterHeaderInfo"></param>
/// <returns></returns>
public ExporterHeaderInfo Filter(ExporterHeaderInfo exporterHeaderInfo)
{
if (exporterHeaderInfo.DisplayName.Equals("姓名"))
{
exporterHeaderInfo.DisplayName = "name";
}
return exporterHeaderInfo;
}
}With the above code snippet we implement IExporterHeaderFilter interface, IExporterHeaderFilter to support multi-language, dynamic control column display and other scenarios
class Program
{
static async Task Main(string[] args)
{
IExporter exporter = new ExcelExporter();
// 生成测试数据
var personList = GenFu.GenFu.ListOf<Person>();
// 导出一个只包含"FirstName", "LastName"列的excel
string fields = "FirstName,LastName"; // 可自定义导出想要的字段
var expandoObjectList = new List<ExpandoObject>(personList.Count);
var propertyInfoList = new List<PropertyInfo>();
var fieldsAfterSplit = fields.Split(',');
foreach (var field in fieldsAfterSplit)
{
var propertyName = field.Trim();
var propertyInfo = typeof(Person).GetProperty(propertyName);
if (propertyInfo == null)
{
throw new Exception($"Property: {propertyName} 没有找到：{typeof(Person)}");
}
propertyInfoList.Add(propertyInfo);
}
foreach (var person in personList)
{
var shapedObj = new ExpandoObject();
foreach (var propertyInfo in propertyInfoList)
{
var propertyValue = propertyInfo.GetValue(person);
((IDictionary<string, object>)shapedObj).Add(propertyInfo.Name, propertyValue);
}
expandoObjectList.Add(shapedObj);
}
string filePath = Path.Combine(Directory.GetCurrentDirectory(), "dynamicExportExcel.xlsx");
var result = await exporter.ExportAsByteArray<ExpandoObject>(expandoObjectList);
File.WriteAllBytes(filePath, result);
}
}
class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string Title { get; set; }
public int Age { get; set; }
public int NumberOfKids { get; set; }
}With the code as above we can export Excel as shown below
[ValueMapping(text: "yes", true)]
[ValueMapping(text: "no", false)]
[ValueMapping(text: "小微客户", 0)]